
import openpyxl
from openpyxl import Workbook 
import re
import os

data = [
    ['证券代码', '证券名称', '2013年', '2014年', '2015年', '2016年', '2017年', '2018年', '2019年', '2020年', '2021年', '2022年', '2023年'],
    ['000568.SZ', '泸州老窖', 56.98, 47.61, 49.4, 62.43, 71.93, 77.53, 80.62, 83.05, 85.7, 86.59, 88.3],
    ['000596.SZ', '古井贡酒', 69.78, 68.63, 71.27, 74.68, 76.43, 77.76, 76.71, 75.23, 75.1, 77.17, 79.07],
    ['000799.SZ', '酒鬼酒', 71.11, 59.87, 70.47, 74.99, 77.94, 78.83, 77.75, 78.87, 79.97, 79.63, 78.35],
    ['000858.SZ', '五 粮 液', 73.26, 72.53, 69.2, 70.2, 72.01, 73.8, 74.46, 74.16, 75.35, 75.42, 75.79],
    ['000860.SZ', '顺鑫农业', 31.2, 33.67, 37.28, 34.54, 33.92, 39.96, 36.2, 28.36, 27.91, 31.73, 31.8],
    ['000995.SZ', '皇台酒业', 64.03, 60.32, 58.92, 18.74, 36.07, 24.07, 75.0, 66.79, 59.04, 63.82, 61.18],
    ['002304.SZ', '洋河股份', 60.42, 60.62, 61.91, 63.9, 66.46, 73.7, 71.35, 72.27, 75.32, 74.6, 75.25],
    ['600197.SH', '伊力特', 48.12, 52.35, 51.41, 50.21, 47.73, 49.01, 51.45, 48.61, 51.45, 48.05, 48.23],
    ['600199.SH', '金种子酒', 66.04, 62.35, 59.58, 55.26, 52.75, 51.25, 37.87, 27.72, 28.8, 26.45, 40.32],
    ['600519.SH', '贵州茅台', 92.94, 92.74, 92.41, 91.51, 90.27, 91.55, 91.64, 91.68, 91.79, 92.09, 92.12],
    ['600559.SH', '老白干酒', 50.72, 58.17, 56.94, 59.23, 62.42, 61.15, 61.47, 64.74, 67.32, 68.36, 67.15],
    ['600702.SH', '舍得酒业', 60.41, 50.66, 50.44, 64.16, 74.62, 72.63, 76.2, 75.87, 77.81, 77.72, 74.5],
    ['600779.SH', '水井坊', 69.93, 68.09, 75.3, 76.16, 79.06, 81.87, 82.87, 84.19, 84.51, 84.49, 83.16],
    ['600809.SH', '山西汾酒', 75.07, 67.44, 67.37, 68.68, 69.84, 66.21, 71.92, 72.15, 74.91, 75.36, 75.31],
    ['603198.SH', '迎驾贡酒', 53.58, 54.57, 58.33, 61.77, 60.64, 60.92, 64.36, 67.13, 67.54, 68.02, 71.37],
    ['603369.SH', '今世缘', 70.58, 70.59, 70.0, 70.96, 71.76, 72.91, 72.81, 71.13, 74.62, 76.6, 78.35],
    ['603589.SH', '口子窖', 62.93, 67.59, 69.83, 72.44, 72.9, 74.37, 74.97, 75.17, 73.9, 74.16, 75.19],
    ['603919.SH', '金徽酒', 54.62, 57.15, 60.1, 61.33, 63.01, 62.3, 60.72, 62.51, 63.74, 62.78, 62.44],
]

# 获取年份列表
years = data[0][2:]

# 对每一年的数据进行处理
for year_index, year in enumerate(years, start=2):
    # 提取每一年的所有证券数据
    yearly_data = [row[year_index] for row in data[1:]]
    print("===========11111111111")
    print(yearly_data)
    # 计算每个证券在该年的排名
    sorted_values = sorted(yearly_data, reverse=True)
    rankings = {value: rank for rank, value in enumerate(sorted_values, start=1)}
    ranks = []
    for value in yearly_data:
        print(f"{value} 的排名是: {rankings[value]}")
        ranks.append(rankings[value])
    print(ranks)
    # 将排名添加到对应的证券数据列表的末尾
    for security_index, rank in enumerate(ranks):
        print(security_index, rank)
        data[security_index + 1].append(rank)

# 打印结果
for row in data:
    print(row)


# print("+++++++++++++++++++++++++++++++++")
# # 给定的数组
# values = [56.98, 69.78, 71.11, 73.26, 31.2, 64.03, 60.42, 48.12, 66.04, 92.94, 50.72, 60.41, 69.93, 75.07, 53.58, 70.58, 62.93, 54.62]

# # 对数组进行降序排序
# sorted_values = sorted(values, reverse=True)
# print(sorted_values)
# # 使用enumerate为排序后的数组中的每个元素分配排名
# rankings = {value: rank for rank, value in enumerate(sorted_values, start=1)}
# print(rankings)
# # 输出每个元素及其排名
# rank = []
# for value in values:
#     print(f"{value} 的排名是: {rankings[value]}")
#     rank.append(rankings[value])

# print(rank)

import re

# 给定的数据
data = [
   ('证券代码', '证券名称', '营业成本\n[报告期]2013年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2014年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2015年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2016年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2017年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2018年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2019年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2020年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2021年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2022年年报\n[报告类型]合并报表\n[单位]亿元\n', '营业成本\n[报告期]2023年年报\n[报告类型]合并报表\n[单位]亿元\n'),
('000568.SZ', '泸州老窖', 44.8714519231, 28.0447342642, 34.9174636856, 31.1954227893, 29.1781505652, 29.3400185891, 30.6541804838, 28.2348455806, 29.5243148831, 33.6952839402, 35.3715140315),
('000596.SZ', '古井贡酒', 13.8413709108, 14.5907291324, 15.0953609928, 15.2358577951, 16.4258805643, 19.3206483765, 24.2604692489, 25.4981494476, 33.0407701192, 38.1632204501, 42.3985090691),
('000799.SZ', '酒鬼酒', 1.9778837925, 1.5590511851, 1.7752661338, 1.6376445596, 1.9373495633, 2.5121287008, 3.3636681351, 3.8586213543, 6.8400360368, 8.2489828638, 6.1265890583),
('000858.SZ', '五 粮 液', 66.1041085224, 57.7202940403, 66.7196326967, 73.1425245238, 84.5008727136, 104.8678293427, 128.0225994734, 148.1196147865, 163.1877858882, 181.7842565964, 201.5714395221),
('000860.SZ', '顺鑫农业', 62.4154581528, 62.8807777315, 60.4426855527, 73.2956395803, 77.5336271562, 72.4959610371, 95.0676213891, 111.1160410971, 107.1962189255, 79.7234783554, 72.2396165884),
('000995.SZ', '皇台酒业', 0.3880730709, 0.2272659357, 0.4294086245, 1.4451157672, 0.3043357749, 0.1934874406, 0.2476495557, 0.3376770609, 0.3730686559, 0.4935204152, 0.5982203662),
('002304.SZ', '洋河股份', 59.4571570935, 57.7741289421, 61.1506909767, 62.0297882859, 66.8114856216, 63.5324219827, 66.2636225124, 58.5190459671, 62.553975641, 76.4553326472, 82.0024525542),
('600197.SH', '伊力特', 9.094426837, 7.7587084474, 7.9561776147, 8.4290605579, 10.0300731489, 10.830358575, 11.1747262425, 9.2615683611, 9.4084135204, 8.4325142711, 11.5519733648),
('600199.SH', '金种子酒', 7.0656932948, 7.813486485, 6.9836130122, 6.4239725631, 6.0961975325, 6.4090473138, 5.6791187014, 7.5017147127, 8.6245001844, 8.7216617381, 8.7690386728),
('600519.SH', '贵州茅台', 21.9392030799, 23.3855053233, 25.3833744906, 34.1010408597, 59.4043637197, 65.2292183377, 74.3001394512, 81.5400147628, 89.8337780996, 100.9346861663, 118.6727385178),
('600559.SH', '老白干酒', 8.884025415, 8.8228573643, 10.0568804908, 9.9401580252, 9.5270571442, 13.9199470212, 15.5269705238, 12.6869575434, 13.1606597509, 14.7243683886, 17.2689365577),
('600702.SH', '舍得酒业', 5.6164434046, 7.1297451553, 5.7301585121, 5.2386638852, 4.1589618823, 6.0560978167, 6.3060345576, 6.5250462182, 11.0247776057, 13.4897959661, 18.0552931863),
('600779.SH', '水井坊', 1.460512728, 1.1643916197, 2.1114733363, 2.8040144559, 4.2885300145, 5.1121200252, 6.063067769, 4.7530488971, 7.1735653164, 7.2468045701, 8.3417793488),
('600809.SH', '山西汾酒', 15.1765124053, 12.752224675, 13.4720906486, 13.7953558068, 18.2101965765, 31.6983281992, 33.3571328721, 38.9551199082, 50.1058008791, 64.5991631719, 78.8434741162),
('603198.SH', '迎驾贡酒', 15.7593753147, 13.4424239712, 12.1959812203, 11.616205129, 12.3534091262, 13.632524488, 13.4629717244, 11.3452765533, 14.8566075486, 17.603708215, 19.2382680465),
('603369.SH', '今世缘', 7.4000223835, 7.0566341886, 7.2735488398, 7.4169623957, 8.3526723281, 10.1354361865, 13.2512302781, 14.7867409805, 16.2640776215, 18.4547966972, 21.8717203351),
('603589.SH', '口子窖', 9.0723964251, 7.3201739819, 7.795276489, 7.7996807704, 9.7649553671, 10.9422890434, 11.6926108629, 9.9588724135, 13.1240240185, 13.2682312391, 14.7903261039),
('603919.SH', '金徽酒', 4.9601402417, 4.3400968568, 4.717703847, 4.9389018526, 4.9305246373, 5.5135134788, 6.4191183692, 6.4888517963, 6.4847746772, 7.4870350996, 9.5696330866),
(None, None, None, None, None, None, None, None, None, None, None, None, None),
(None, None, None, None, None, None, None, None, None, None, None, None, None),
(None, None, None, None, None, None, None, None, None, None, None, None, None),
(None, None, None, None, None, None, None, None, None, None, None, None, None),
(None, None, None, None, None, None, None, None, None, None, None, None, None),
('数据来源：东方财富Choice数据', None, None, None, None, None, None, None, None, None, None, None, None),
]


def get_recent_n_years_data(year_num):
    # 提取年份信息
    years = [re.search(r'\d{4}', item).group() for item in data[0][2:] if re.search(r'\d{4}', item)]
    print(years)
    # 确定最近5年
    recent_years = sorted(years, key=lambda x: int(x))[-year_num:]
    print(recent_years)
    # 初始化一个列表来存储最近5年的数据
    recent_data = []

    # 遍历数据，提取最近5年的数据
    for row in data[0:]:
        if row[1] is None:  # 跳过空行
            continue
        recent_row = [row[0], row[1]]  # 证券代码和名称
        for year in recent_years:
            for index, item in enumerate(data[0][2:]):
                if str(year) in item:
                    recent_row.append(row[index + 2])
        recent_data.append(recent_row)

    # 打印最近5年的数据
    for row in recent_data:
        print(row)

    return recent_data

# get_recent_n_years_data(6)

data = [
    ['证券代码', '证券名称', '2013年', '2014年', '2015年', '2016年', '2017年', '2018年', '2019年', '2020年', '2021年', '2022年', '2023年', '2013年排名', '2014年排名', '2015年排名', '2016年排名', '2017年排名', '2018年排名', '2019年排名', '2020年排名', '2021年排名', '2022年排名', '2023年排名'],
['000568.SZ', '泸州老窖', 56.98, 47.61, 49.4, 62.43, 71.93, 77.53, 80.62, 83.05, 85.7, 86.59, 88.3, 13, 17, 17, 11, 8, 5, 3, 3, 2, 2, 2],
['000596.SZ', '古井贡酒', 69.78, 68.63, 71.27, 74.68, 76.43, 77.76, 76.71, 75.23, 75.1, 77.17, 79.07, 7, 4, 3, 4, 4, 4, 5, 6, 8, 6, 4],
['000799.SZ', '酒鬼酒', 71.11, 59.87, 70.47, 74.99, 77.94, 78.83, 77.75, 78.87, 79.97, 79.63, 78.35, 4, 11, 4, 3, 3, 3, 4, 4, 4, 4, 6],
['000858.SZ', '五 粮 液', 73.26, 72.53, 69.2, 70.2, 72.01, 73.8, 74.46, 74.16, 75.35, 75.42, 75.79, 3, 2, 7, 7, 7, 7, 9, 8, 6, 8, 7],
['000860.SZ', '顺鑫农业', 31.2, 33.67, 37.28, 34.54, 33.92, 39.96, 36.2, 28.36, 27.91, 31.73, 31.8, 18, 18, 18, 17, 18, 17, 18, 17, 18, 17, 18],
['000995.SZ', '皇台酒业', 64.03, 60.32, 58.92, 18.74, 36.07, 24.07, 75.0, 66.79, 59.04, 63.82, 61.18, 9, 10, 12, 18, 17, 18, 7, 13, 15, 14, 15],
['002304.SZ', '洋河股份', 60.42, 60.62, 61.91, 63.9, 66.46, 73.7, 71.35, 72.27, 75.32, 74.6, 75.25, 11, 9, 9, 10, 11, 8, 12, 9, 7, 10, 9],
['002304.SZ', '洋河股份', 60.42, 60.62, 61.91, 63.9, 66.46, 73.7, 71.35, 72.27, 75.32, 74.6, 75.25, 11, 9, 9, 10, 11, 8, 12, 9, 7, 10, 9],
['600197.SH', '伊力特', 48.12, 52.35, 51.41, 50.21, 47.73, 49.01, 51.45, 48.61, 51.45, 48.05, 48.23, 17, 15, 15, 16, 16, 16, 16, 16, 16, 16, 16],
['600199.SH', '金种子酒', 66.04, 62.35, 59.58, 55.26, 52.75, 51.25, 37.87, 27.72, 28.8, 26.45, 40.32, 8, 8, 11, 15, 15, 15, 17, 18, 17, 18, 17],
['600519.SH', '贵州茅台', 92.94, 92.74, 92.41, 91.51, 90.27, 91.55, 91.64, 91.68, 91.79, 92.09, 92.12, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
['600559.SH', '老白干酒', 50.72, 58.17, 56.94, 59.23, 62.42, 61.15, 61.47, 64.74, 67.32, 68.36, 67.15, 16, 12, 14, 14, 13, 13, 14, 14, 13, 12, 13],
['600702.SH', '舍得酒业', 60.41, 50.66, 50.44, 64.16, 74.62, 72.63, 76.2, 75.87, 77.81, 77.72, 74.5, 12, 16, 16, 9, 5, 10, 6, 5, 5, 5, 11],
['600779.SH', '水井坊', 69.93, 68.09, 75.3, 76.16, 79.06, 81.87, 82.87, 84.19, 84.51, 84.49, 83.16, 6, 5, 2, 2, 2, 2, 2, 2, 3, 3, 3],
['600809.SH', '山西汾酒', 75.07, 67.44, 67.37, 68.68, 69.84, 66.21, 71.92, 72.15, 74.91, 75.36, 75.31, 2, 7, 8, 8, 10, 11, 11, 10, 9, 9, 8],
['603198.SH', '迎驾贡酒', 53.58, 54.57, 58.33, 61.77, 60.64, 60.92, 64.36, 67.13, 67.54, 68.02, 71.37, 15, 14, 13, 12, 14, 14, 13, 12, 12, 13, 12],
['603369.SH', '今世缘', 70.58, 70.59, 70.0, 70.96, 71.76, 72.91, 72.81, 71.13, 74.62, 76.6, 78.35, 5, 3, 5, 6, 9, 9, 10, 11, 10, 7, 6],
['603589.SH', '口子窖', 62.93, 67.59, 69.83, 72.44, 72.9, 74.37, 74.97, 75.17, 73.9, 74.16, 75.19, 10, 6, 6, 5, 6, 6, 8, 7, 11, 11, 10],
['603919.SH', '金徽酒', 54.62, 57.15, 60.1, 61.33, 63.01, 62.3, 60.72, 62.51, 63.74, 62.78, 62.44, 14, 13, 10, 13, 12, 12, 15, 15, 14, 15, 14],
]

# 添加综合排名的标题
data[0].append('综合排名')

# 计算综合排名
for i in range(1, len(data)):
    # 获取该证券的排名数据
    rankings = data[i][17:-1]  # 假设排名数据从第17列开始，到倒数第二列结束
    # 计算平均排名，排除非数字值
    average_rank = sum(int(ranking) for ranking in rankings if ranking.isdigit()) / len(rankings)
    # 四舍五入保留两位小数
    average_rank = round(average_rank, 2)
    # 添加平均排名到数据行的末尾
    data[i].append(average_rank)

# 打印结果
for row in data:
    print(row)
